package com.xuan_poi.apache_poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.OutputStream;

/**
 * @author renzhengxuan
 * @date 2021/3/31
 */
public class ExcelWrite {
    /**
     * 写入
     * 创建03版本的excel
     */
    @Test
    public void excelWriteTest03() throws Exception {
        //首先创建一个工作簿(如果是03版本就new HSSFWorkbook，如果是07版本就new XSSFWorkbook)
        Workbook workbook = new XSSFWorkbook();
        //创建sheet
        Sheet sheet = workbook.createSheet();
        //创建第一行
        Row row = sheet.createRow(0);
        //创建第一行的第一个单元格
        Cell cell = row.createCell(0);
        //像第一个单元个中加入内容
        cell.setCellValue("姓名");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("年龄");

        //创建第一行
        Row row1 = sheet.createRow(1);
        //创建第一行的第一个单元格
        Cell cell2 = row1.createCell(0);
        //像第一个单元个中加入内容
        cell2.setCellValue("轩");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("23");
        //单元格创建完毕，之后就生成一张表
        FileOutputStream outputStream = new FileOutputStream("D:\\excel\\excel导出.xlsx");
        //输出
        workbook.write(outputStream);
        //关闭流
        outputStream.close();
        System.out.println("输出完成");
    }

    /**
     * 03版本大数据量生成
     *
     * @throws Exception
     */
    @Test
    public void excelWriteBigData03() throws Exception {
        long startTime = System.currentTimeMillis();
        //03版本的excel
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        OutputStream fileOutputStream = new FileOutputStream("D:\\excel\\大数据量导出.xls");
        workbook.write(fileOutputStream);
        System.out.println("文件写入完成");
        fileOutputStream.close();
        long endTime = System.currentTimeMillis();
        System.out.println("03版本所用时间：" + (double) (endTime - startTime) / 1000);
    }

    /**
     * 07版
     *
     * @throws Exception
     */
    @Test
    public void excelWriteBigData07() throws Exception {
        long startTime = System.currentTimeMillis();
        //03版本的excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        OutputStream fileOutputStream = new FileOutputStream("D:\\excel\\大数据量导出.xlsx");
        workbook.write(fileOutputStream);
        System.out.println("文件写入完成");
        fileOutputStream.close();
        long endTime = System.currentTimeMillis();
        System.out.println("07版本所用时间：" + (double) (endTime - startTime) / 1000);
    }

    /**
     * 07升级版
     *
     * @throws Exception
     */
    @Test
    public void excelWriteBigData07S() throws Exception {
        long startTime = System.currentTimeMillis();
        //03版本的excel
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        OutputStream fileOutputStream = new FileOutputStream("D:\\excel\\excelWriteBigData07S.xlsx");
        workbook.write(fileOutputStream);
        System.out.println("文件写入完成");
        fileOutputStream.close();
        ((SXSSFWorkbook) workbook).dispose();//关闭临时文件
        long endTime = System.currentTimeMillis();
        System.out.println("07版本升级版所用时间：" + (double) (endTime - startTime) / 1000);
    }
}
